<?php
/**
 * DBERP 进销存系统
 *
 * ==========================================================================
 * @link      http://www.dberp.net/
 * @copyright 北京珑大钜商科技有限公司，并保留所有权利。
 * @license   http://www.dberp.net/license.html License
 * ==========================================================================
 *
 * @author    静静的风 <baron@loongdom.cn>
 *
 */

namespace Store\Repository;

use Admin\Data\Common;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Store\Entity\Goods;
use Store\Entity\Unit;
use Store\Entity\WarehouseGoods;

class GoodsRepository extends EntityRepository
{
    /**
     * 获取最大商品id
     * @return int|mixed|string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function getMaxGoodsId()
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('MAX(g.goodsId) as maxGoodsId')->from(Goods::class, 'g');

        $maxId = $query->getQuery()->getSingleScalarResult();

        return $maxId == null ? 1 : $maxId + 1;
    }

    /**
     * 抛出获取商品的sql
     * @param array $search
     * @return \Doctrine\ORM\Query
     */
    public function findAllGoods($search = [])
    {
        $query = $this->getEntityManager()->createQueryBuilder()
            ->select('g', 'c', 'b')
            //->addSelect('(SELECT SUM(w.warehouseGoodsStock) FROM Store\Entity\WarehouseGoods w WHERE w.goodsId = g.goodsId) AS warehouse_goods_num')
            ->from(Goods::class, 'g')
            ->join('g.goodsCategory', 'c')
            ->leftJoin('g.brand', 'b')
            ->orderBy('g.goodsSort', 'ASC')
            ->addOrderBy('g.goodsId', 'DESC');

        $query = $this->querySearchData($search, $query);

        return $query->getQuery();
    }

    /**
     * ajax商品检索
     * @param $searchName
     * @param $searchGoodsCategoryId
     * @param $exclude
     * @return Query
     */
    public function ajaxFindAllGoods($searchName, $searchGoodsCategoryId, $exclude = ''): Query
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('g')
            ->from(Goods::class, 'g')
            ->where($query->expr()->orX()->add($query->expr()->like('g.goodsName', "'%".$searchName."%'"))->add($query->expr()->like('g.goodsNumber', "'%".$searchName."%'")))
            ->orderBy('g.goodsSort', 'ASC')
            ->addOrderBy('g.goodsId', 'DESC');
        if ($searchGoodsCategoryId > 0) $query->andWhere('g.goodsCategoryId = :goodsCategoryId')->setParameter('goodsCategoryId', $searchGoodsCategoryId);
        if ($exclude == 'serialNumber') $query->andWhere('g.goodsSerialNumberState = 0');

        return $query->getQuery();
    }

    /**
     * 库存预警列表
     * @param $warningMinNum
     * @param $warningMaxNum
     * @param array $search
     * @return Query
     */
    public function findStockWarningGoodsList($warningMinNum, $warningMaxNum, array $search = []): Query
    {
        $query = $this->getEntityManager()->createQueryBuilder();

        if ($warningMaxNum == 0 && $warningMinNum == 0) {
            $query->select('g')->from(Goods::class, 'g')->where('g.goodsId < 0');
            return $query->getQuery();
        }

        $query->select('g', 'c', 'b')
            ->from(Goods::class, 'g')
            ->join('g.goodsCategory', 'c')
            ->leftJoin('g.brand', 'b')
            ->where('g.goodsStock > 0')
            ->orderBy('g.goodsSort', 'ASC')
            ->addOrderBy('g.goodsId', 'DESC');

        $orX = $query->expr()->orX();
        if ($warningMinNum > 0) $orX->add($query->expr()->lte('g.goodsStock', $warningMinNum));
        if ($warningMaxNum > 0) $orX->add($query->expr()->gte('g.goodsStock', $warningMaxNum));
        $query->andWhere($orX);

        $query = $this->querySearchData($search, $query);

        return $query->getQuery();
    }

    /**
     * 获取对应goodsId数组的商品信息
     * @param array $goodsId
     * @param int $warehouseId
     * @return float|int|mixed[]|string
     */
    public function findGoodsArray(array $goodsId, int $warehouseId = -1)
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('g')
            ->addSelect('(SELECT u.unitName FROM '.Unit::class.' AS u WHERE u.unitId=g.unitId) AS unitName')
            ->from(Goods::class, 'g')
            ->where($query->expr()->in('g.goodsId', $goodsId));

        if ($warehouseId >= 0) $query->addSelect('(SELECT w.warehouseGoodsStock FROM '.WarehouseGoods::class.' AS w WHERE w.warehouseId='.$warehouseId.' and w.goodsId=g.goodsId) AS warehouseGoodsNum');

        return $query->getQuery()->getArrayResult();
    }

    /**
     * 检索商品名称
     * @param $goodsName
     * @return mixed
     */
    public function findGoodsNameSearch($goodsName)
    {
        //当定义为某个字段时，输出的是数组
        $query = $this->getEntityManager()->createQueryBuilder()
            ->select('g.goodsId,g.goodsName,g.goodsSpec')
            ->from(Goods::class, 'g')
            ->where('g.goodsName LIKE \'%'.$goodsName.'%\'')
            ->setMaxResults(10);

        $goodsResult = $query->getQuery()->getResult();
        return $goodsResult;
    }

    private function querySearchData($search, QueryBuilder $queryBuilder)
    {
        if(isset($search['start_id']) && $search['start_id'] > 0)                   $queryBuilder->andWhere($queryBuilder->expr()->gte('g.goodsId', $search['start_id']));
        if(isset($search['end_id']) && $search['end_id'] > 0)                       $queryBuilder->andWhere($queryBuilder->expr()->lte('g.goodsId', $search['end_id']));
        if(isset($search['start_price']) && $search['start_price'] > 0)             $queryBuilder->andWhere($queryBuilder->expr()->gte('g.goodsPrice', $search['start_price']));
        if(isset($search['end_price']) && $search['end_price'] > 0)                 $queryBuilder->andWhere($queryBuilder->expr()->lte('g.goodsPrice', $search['end_price']));
        if(isset($search['start_sales_price']) && $search['start_sales_price'] > 0) $queryBuilder->andWhere($queryBuilder->expr()->gte('g.goodsRecommendPrice', $search['start_sales_price']));
        if(isset($search['end_sales_price']) && $search['end_sales_price'] > 0)     $queryBuilder->andWhere($queryBuilder->expr()->lte('g.goodsRecommendPrice', $search['end_sales_price']));
        if(isset($search['start_stock']) && $search['start_stock'] > 0)             $queryBuilder->andWhere($queryBuilder->expr()->gte('g.goodsStock', $search['start_stock']));
        if(isset($search['end_stock']) && $search['end_stock'] > 0)                 $queryBuilder->andWhere($queryBuilder->expr()->lte('g.goodsStock', $search['end_stock']));
        if(isset($search['goods_category_id']) && $search['goods_category_id'] > 0) $queryBuilder->andWhere($queryBuilder->expr()->eq('g.goodsCategoryId', $search['goods_category_id']));
        if(isset($search['brand_id']) && $search['brand_id'] > 0)                   $queryBuilder->andWhere($queryBuilder->expr()->eq('g.brandId', $search['brand_id']));

        if(isset($search['goods_name']) && !empty($search['goods_name']))           $queryBuilder->andWhere($queryBuilder->expr()->like('g.goodsName', "'%".$search['goods_name']."%'"));
        if(isset($search['goods_number']) && !empty($search['goods_number']))       $queryBuilder->andWhere($queryBuilder->expr()->like('g.goodsNumber', "'%".$search['goods_number']."%'"));
        if(isset($search['goods_spec']) && !empty($search['goods_spec']))           $queryBuilder->andWhere($queryBuilder->expr()->like('g.goodsSpec', "'%".$search['goods_spec']."%'"));

        return $queryBuilder;
    }
}